package cqie.frame.controller;



import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.api.ApiController;
import com.baomidou.mybatisplus.extension.api.R;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sun.org.apache.regexp.internal.RE;
import cqie.frame.config.MD5;
import cqie.frame.core.PageParam;
import cqie.frame.entity.Clazz;
import cqie.frame.entity.Emp;
import cqie.frame.entity.Student;
import cqie.frame.entity.SysUser;
import cqie.frame.entity.po.SelectListItem;
import cqie.frame.entity.qo.StudentQo;
import cqie.frame.entity.vo.StudentVo;
import cqie.frame.response.CodeMsg;
import cqie.frame.response.ResultJson;
import cqie.frame.service.IClazzService;
import cqie.frame.service.IStudentService;
import cqie.frame.service.ISysUserService;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.hibernate.internal.util.StringHelper;
import org.springframework.beans.BeanUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;
import org.thymeleaf.util.StringUtils;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 学生(Student)表控制层
 *
 * @author makejava
 * @since 2021-09-30 10:32:27
 */
@RestController
@RequestMapping("student")
public class StudentController extends ApiController {
    /**
     * 服务对象
     */
    @Resource
    private IStudentService iStudentService;
    @Resource
    private IClazzService iClazzService;
    @Resource
    private ISysUserService iSysUserService;



    //新增员学生
    @ApiOperation(value = "新增学生", notes = "新增学生，同时更新学生表。", response = Student.class)
    @PostMapping(value = "/addStudent")
    public ResultJson addEmp(@RequestBody Student po) {
        try {
            //构造mo
            Student student = new Student();
            //复制po的属性到emp,第三个参数可选，为忽略某些属性
            BeanUtils.copyProperties(po, student);
            //插入用户表后回显插入用户的主键id
            iStudentService.save(student);
            po.setId(student.getId());
            //返回学生
            return ResultJson.success(po);
        } catch (Exception e) {
            return ResultJson.error(CodeMsg.SERVER_ERROR);
        }
    }

    //删除选中学生，多选
    @ApiOperation(value = "删除选中学生", notes = "删除选中学生，同时更新员工表。")
    @PostMapping(value = "/deleteCheckStudent")
    public ResultJson deleteCheckStudent(@RequestBody Long[] studentIdList) {
        try {
            for (int i = 0; i < studentIdList.length; i++) {
                iStudentService.removeById(studentIdList[i]);
            }
            return ResultJson.success("删除成功");
        } catch (Exception e) {
            return ResultJson.error(CodeMsg.SERVER_ERROR);
        }
    }

    //删除学生
    @ApiOperation(value = "删除单个学生", notes = "删除单个学生，同时更新学生表。")
    @GetMapping(value = "/deleteStudent")
    public ResultJson deleteStudent(long id) {
        try {
            //删除学生表
            iStudentService.removeById(id);
            //返回前端
            return ResultJson.success("删除成功");
        } catch (Exception e) {
            return ResultJson.error(CodeMsg.SERVER_ERROR);
        }
    }


    //修改员工
    @ApiOperation(value = "修改学生", notes = "修改学生，同时更新学生表。")
    @PostMapping(value = "/updateStudent")
    public ResultJson updateStudent(@RequestBody Student po) {
        try {
            //获取修改前的值
            Student student = new Student();
            //复制po的属性到sysUser,第三个参数可选，为忽略某些属性
            BeanUtils.copyProperties(po, student);
            //修改学生
            iStudentService.updateById(student);
            return ResultJson.success("修改成功");
        } catch (Exception e) {
            return ResultJson.error(CodeMsg.SERVER_ERROR);
        }
    }
    //查询学生
    @ApiOperation(value = "分页列表", response = StudentVo.class)
    @PostMapping(value = "/listPaged")
    public ResultJson listPaged(@RequestBody StudentQo qo) {
        try {
            Page<Student> page = new Page<>(qo.getPageIndex(), qo.getPageSize());
            //1.查询条件
            QueryWrapper<Student> qw = new QueryWrapper<>();
            if (!StringUtils.isEmpty(qo.getName())) {
                //不为空，启用查询
                //查询模糊查询
                qw.like("name", qo.getName());
            }
            //是否查询性别
            if (qo.getSex() != null) {
                //不为空，启用查询
                qw.eq("sex", qo.getSex());
            }
            //是否查询代码
            if (!StringUtils.isEmpty(qo.getCode())) {
                //不为空，启用查询
                //查询模糊查询
                qw.like("code", qo.getCode());
            }
            //是否查询班级
            if (qo.getClazzId() != null) {
                //不为空，启用查询
                //查询模糊查询
                qw.like("clazzId", qo.getClazzId());
            }
            //2.执行查询
            IPage<Student> datas = iStudentService.page(page, qw);
            //3.生成返回值
            //映射准备--用户
            List<Long> userIdList = datas.getRecords()
                    .stream()
                    .filter(x -> x.getUserId() != null)
                    .map(Student::getUserId)
                    .distinct()
                    .collect(Collectors.toList());
            List<SysUser> allUser = new ArrayList<>();
            if (userIdList.size() > 0) {
                QueryWrapper<SysUser> qwUser = new QueryWrapper<>();
                qwUser.in("id", userIdList);
                allUser = iSysUserService.list(qwUser);
            }
            //映射准备--所在班级
            List<Long> clazzIdList = datas.getRecords()
                    .stream()
                    .filter(x -> x.getClazzId() != null)
                    .map(Student::getClazzId)
                    .distinct()
                    .collect(Collectors.toList());
            List<Clazz> allClazz = new ArrayList<>();
            if (clazzIdList.size() > 0) {
                QueryWrapper<Clazz> qwClazz = new QueryWrapper<>();
                qwClazz.in("id", clazzIdList);
                allClazz = iClazzService.list(qwClazz);
            }
            //映射
            List<StudentVo> list = new ArrayList<>();
            IPage<StudentVo> ret = new Page<>();
            BeanUtils.copyProperties(datas, ret);
            for (Student mo : datas.getRecords()) {
                StudentVo vo = new StudentVo();
                BeanUtils.copyProperties(mo, vo);
                //映射--性别
                vo.setSexName(mo.getSex() == 0 ? "男" : "女");
                //映射--登录账号
                if (mo.getUserId() != null) {
                    SysUser findUser = allUser.stream().filter(x -> x.getId().equals(mo.getUserId())).findFirst().orElse(null);
                    if (findUser != null) {
                        vo.setUserAccount(findUser.getAccount());
                    }
                }
                //映射--所在班级
                if (mo.getClazzId() != null) {
                    Clazz findClazz = allClazz.stream().filter(x -> x.getId().equals(mo.getClazzId())).findFirst().orElse(null);
                    if (findClazz != null) {
                        vo.setClazzName(findClazz.getName());
                    }
                }
                list.add(vo);
            }
            ret.setRecords(list);
            return  ResultJson.success(ret);
        } catch (Exception ex) {
            return  ResultJson.error(CodeMsg.SERVER_ERROR);
        }
    }

    @ApiOperation(value = "查看详情", response = StudentVo.class)
    @GetMapping(value = "/details")
    public ResultJson details(Long id) {
        try {
            //1.获取mo
            Student mo = iStudentService.getById(id);
            //2.映射成vo
            StudentVo vo = new StudentVo();
            BeanUtils.copyProperties(mo, vo);
            //映射准备
            //映射--性别
            vo.setSexName(mo.getSex() == 0 ? "男" : "女");
            //映射--登录账号
            if (mo.getUserId() != null) {
                SysUser findUser = iSysUserService.getById(mo.getUserId());
                if (findUser != null) {
                    vo.setUserAccount(findUser.getAccount());
                }
            }
            //映射--所在班级
            if (mo.getClazzId() != null) {
                Clazz findClazz = iClazzService.getById(mo.getClazzId());
                if (findClazz != null) {
                    vo.setClazzName(findClazz.getName());
                }
            }
            //3.返回
            return  ResultJson.success(vo);
        } catch (Exception ex) {
            return  ResultJson.error(CodeMsg.SERVER_ERROR);
        }
    }

    @ApiOperation(value = "获取用户备选项", response = SelectListItem.class)
    @GetMapping(value = "/listAllUser")
    public ResultJson listAllUser(Long studentId) {
        try {
            List<SelectListItem> ret = new ArrayList<>();
            QueryWrapper<SysUser> qw = new QueryWrapper<>();
            String notInSql = "select userId from student where userId is not null";
            qw.eq("status", 0);
            qw.notInSql("id", notInSql);
            List<SysUser> allUser = iSysUserService.list(qw);
            for (SysUser mo : allUser) {
                ret.add(SelectListItem.SelectListItem(mo.getId(), mo.getAccount()));
            }
            if (studentId != null) {
                Student student = iStudentService.getById(studentId);
                if (student.getUserId() != null) {
                    SysUser sysUser = iSysUserService.getById(student.getUserId());
                    ret.add(SelectListItem.SelectListItem(sysUser.getId(), sysUser.getAccount()));
                }
            }
            return  ResultJson.success(ret);
        } catch (Exception ex) {
            return  ResultJson.error(CodeMsg.SERVER_ERROR);
        }
    }

    @ApiOperation(value = "获取班级备选项", response = SelectListItem.class)
    @GetMapping(value = "/listAllClazz")
    public ResultJson listAllClazz() {
        try {
            List<SelectListItem> ret = new ArrayList<>();
            QueryWrapper<Clazz> qw = new QueryWrapper<>();
            qw.eq("status", 0);
            List<Clazz> allClazz = iClazzService.list(qw);
            for (Clazz mo : allClazz) {
                ret.add(SelectListItem.SelectListItem(mo.getId(), mo.getCode()+"————"+mo.getName()));
            }
            return  ResultJson.success(ret);
        } catch (Exception ex) {
            return  ResultJson.error(CodeMsg.SERVER_ERROR);
        }
    }

    @ApiOperation(value ="下载模板", notes = "下载导入学生的Excel模板")
    @PostMapping(value = "/downloadTemplate")
    public void downloadTemplate() {
        OutputStream out = null;
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("sheet1");
            sheet.setDefaultColumnWidth(15);

            HSSFRow row;
            HSSFCell cell;
            // 标题行
            HSSFCellStyle styleColHead = getColumnTopStyle(workbook);
            String[] headers = new String[]{"序号", "姓名", "学号", "性别", "联系电话", "班级代码", "班级名称"};
            row = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                cell = row.createCell(i);
                cell.setCellStyle(styleColHead);
                cell.setCellValue(new HSSFRichTextString(headers[i]));
            }
            // 数据行
            HSSFCellStyle styleData = getStyle(workbook);
            List<Student> studentList = iStudentService.list(null);

            int rowCount = studentList.size();
            for (int r = 0; r < rowCount; r++) {
                row = sheet.createRow(r+1);
                // 序号
                cell = row.createCell(0);
                cell.setCellStyle(styleData);
                cell.setCellValue(r+1);
                //姓名
                cell = row.createCell(1);
                cell.setCellStyle(styleData);
                cell.setCellValue(studentList.get(r).getName());
                //学号
                cell = row.createCell(2);
                cell.setCellStyle(styleData);
                cell.setCellValue(studentList.get(r).getCode());
                //性别
                String sex = "男";
                if (studentList.get(r).getSex()==0){
                    sex = "女";
                }
                cell = row.createCell(3);
                cell.setCellStyle(styleData);
                cell.setCellValue(sex);
                //联系电话
                cell = row.createCell(4);
                cell.setCellStyle(styleData);
                if (studentList.get(r).getMobile()!=null) {
                    cell.setCellValue(studentList.get(r).getMobile());
                }else {
                    cell.setCellValue("暂无数据");
                }
                //班级代码
                Clazz clazz = iClazzService.getById(studentList.get(r).getClazzId());

                cell = row.createCell(5);
                cell.setCellStyle(styleData);
                cell.setCellValue(clazz.getCode());
                //班级名称
                cell = row.createCell(6);
                cell.setCellStyle(styleData);
                cell.setCellValue(clazz.getName());
            }
            //输出
            ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
            HttpServletResponse response = attributes.getResponse();
            response.setContentType("application/octet-stream");
            response.setContentType("application/OCTET-STREAM;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=StoreStockRuntime.xls");
            out = new BufferedOutputStream(response.getOutputStream());

            workbook.write(out);
            System.out.println("导出成功");
        } catch (Exception ex) {
            System.out.println("导出失败");
            System.out.println(ex.getMessage());
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (Exception e) {
                }
            }
        }
    }

    @ApiOperation(value = "批量导入", notes = "导入Excel批量添加")
    @PostMapping(value = "/importExcel")
    public ResultJson importExcel(@RequestParam("file") MultipartFile file) {
        try {
            InputStream is = file.getInputStream();
            POIFSFileSystem fs = new POIFSFileSystem(is);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            int totalRows = sheet.getPhysicalNumberOfRows();
            if (totalRows <= 1){
                return  ResultJson.error(new CodeMsg(100, "文件无数据"));
            }
            List<Student> all = new ArrayList<>();
            //数据准备
            List<Student> allStudent = iStudentService.list(null);
            List<Clazz> allClazz = iClazzService.list(null);
            List<SysUser> allUser = iSysUserService.list(null);
            //跳过标题行
            for (int r = 1; r < totalRows; r++) {
                Student mo = new Student();
                HSSFRow row = sheet.getRow(r);
                //"序号", "姓名", "学号", "性别", "联系电话", "班级代码", "班级名称"
                //跳过序号列
                //姓名
                String name = getCellStringValue(row, 1);
                if (StringUtils.isEmpty(name)){
                    return  ResultJson.error(new CodeMsg(100,"第" + r + "行,姓名-无值"));
                }
                mo.setName(name);
                //学号
                String code = getCellStringValue(row, 2);
                if (StringUtils.isEmpty(code)){
                    return  ResultJson.error(new CodeMsg(100, "第" + r + "行,学号-无值"));
                }
                mo.setCode(code);

                //根据学号判断学生是否存在，避免重复添加
                Student findStudent = allStudent.stream().filter(x -> x.getCode().equals(code)).findFirst().orElse(null);
                if(findStudent==null) {
                    //性别
                    String sexName = getCellStringValue(row, 3);
                    if (StringUtils.isEmpty(sexName) || !(sexName.equals("男") || sexName.equals("女"))) {
                        return  ResultJson.error(new CodeMsg(100, "第" + r + "行,性别-无值或值错误"));
                    }
                    mo.setSex(sexName.equals("男") ? 0 : 1);
                    //联系电话
                    String mobile = getCellStringValue(row, 4);
                    mo.setMobile(mobile);
                    //班级
                    String clazzCode = getCellStringValue(row, 5);
                    if (StringUtils.isEmpty(clazzCode)) {
                        return  ResultJson.error(new CodeMsg(100, "第" + r + "行,班级代码-无值"));
                    }
                    String clazzName = getCellStringValue(row, 6);
                    if (StringUtils.isEmpty(clazzName)) {
                        return  ResultJson.error(new CodeMsg(100, "第" + r + "行,班级名称-无值"));
                    }
                    Clazz findClazz = allClazz.stream().filter(x -> x.getCode().equals(clazzCode) && x.getName().equals(clazzName)).findFirst().orElse(null);
                    if (findClazz == null) {
                        //根据班级代码、班级名称自动添加班级
                        //return new RetJson("100", "第" + r + "行,班级-不存在",null);
                        findClazz = new Clazz();
                        findClazz.setCode(clazzCode);
                        findClazz.setName(clazzName);
                        findClazz.setStatus(0);
                        iClazzService.save(findClazz);
                        allClazz.add(findClazz);//更新已经存在的班级，避免重复添加
                    }
                    mo.setClazzId(findClazz.getId());
                    //系统账号
                    SysUser findUser = allUser.stream().filter(x -> x.getAccount().equals(code)).findFirst().orElse(null);//学生登录账号=学号
                    if (findUser == null) {
                        findUser = new SysUser();
                        findUser.setName(name);
                        findUser.setAccount(code);
                        findUser.setPassword(MD5.md5(code));//学生登录密码=学号
                        findUser.setStatus(0);
                        iSysUserService.save(findUser);
                        allUser.add(findUser);//更新已经存在的学生用户，避免重复添加
                    }
                    mo.setUserId(findUser.getId());
                    all.add(mo);

                    allStudent.add(mo);//避免同一批中有重复的学生
                }
            }
            if (all.size() > 0) {
                iStudentService.saveBatch(all);
            }
            return  ResultJson.success(all);
        } catch (Exception ex) {
            return  ResultJson.error(new CodeMsg(900, ex.getMessage()));
        }
    }

    /*
     * 列数据信息单元格样式new
     */
    private HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        //font.setFontHeightInPoints((short)10);
        //字体加粗
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;
    }

    /*
     * 列头单元格样式
     */
    private HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 11);
        //字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置单元格底色
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;
    }

    /*
     * 获取单元格的值
     */
    private String getCellStringValue(HSSFRow row, int colIndex) {
        String cellValue = "";
        try {
            HSSFCell cell = row.getCell((short) colIndex);
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    cellValue = cell.getRichStringCellValue().getString().trim();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    cellValue = cell.getNumericCellValue() + "";
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    //cellValue = cell.getCellFormula() + ""
                    try {
                        cellValue = cell.getStringCellValue();
                    } catch (IllegalStateException e) {
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                default:
                    cellValue = "";
            }
        } catch (Exception e) {

        }
        return cellValue;
    }
}

